﻿CREATE PROCEDURE dbo.SP_MemberPhones_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @MemberId Int   =  NULL 
, @Phone VarChar (50)  =  NULL 
, @PhoneId Int   =  NULL 
, @PhoneType TinyInt   =  NULL 
	AS
			WITH CTE_MemberPhones AS
			( 
			SELECT
 							MemberId,
								Phone,
								PhoneId,
								PhoneType,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='MemberId' THEN MemberId
						 
						WHEN @OrderColume='Phone' THEN Phone
												WHEN @OrderColume='PhoneId' THEN PhoneId
												WHEN @OrderColume='PhoneType' THEN PhoneType
									ELSE 
							    PhoneId
					        	END
			) AS "RowNumber"
  			FROM MemberPhones 
			where 
								(MemberId = @MemberId OR @MemberId Is Null)
										AND(Phone LIKE @Phone OR @Phone Is Null)
										AND(PhoneId = @PhoneId OR @PhoneId Is Null)
										AND(PhoneType = @PhoneType OR @PhoneType Is Null)
								)
			SELECT  *
			FROM CTE_MemberPhones
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			